#!/usr/bin/python

from __future__ import print_function

import sqlite3
import sys

def show_usage():
    print("hdvb-merge-chans FILE1 FILE2 ...")
    print("Merge two or more channels files in sqlite3 format created by")
    print("hdvb-scan, adjusting LCNs so that none are duplicated. FILE1")
    print("has highest priority for keeping its LCNs and so on. All ")
    print("data is merged into FILE1, and lcns in subsequent files may also ")
    print("be altered, so making copies first is recommended.")


# filled_ranges is a list of 2-member lists containing
# start and end of each range.
# [-2, -2] is marked for deletion
def ranges_from_lcn(lcn, filled_ranges):
    extended = False
    for r in filled_ranges:
        if r[0] == lcn + 1:
            extended = True
            r[0] = lcn
            for r2 in filled_ranges:
                if r2[1] == lcn - 1:
                    r2[1] = r[1]
                    r[0] = -2
                    r[1] = -2
                    break
        elif r[1] == lcn - 1:
            extended = True
            r[1] = lcn
            for r2 in filled_ranges:
                if r2[0] == lcn + 1:
                    r[1] = r2[1]
                    r2[0] = -2
                    r2[1] = -2
                    break
    if not extended:
        filled_ranges.append([lcn, lcn])


# See above
def ranges_from_db(db, filled_ranges):
    for row in db.execute("SELECT lcn FROM services ORDER BY lcn").fetchall():
        ranges_from_lcn(row[0], filled_ranges)


# Deletes all [-2, -2] members
def delete_garbage(ranges):
    i = 0
    while i < len(ranges):
        if ranges[i][0] == -2:
            del ranges[i]
        else:
            i += 1


def ranges_overlap(r1, r2):
    return r1[0] != -2 and r1[0] <= r2[1] and r1[1] >= r2[0]


# ranges2 are added to ranges1
def merge_range_lists(ranges1, ranges2):
    ranges1 += ranges2
    l = len(ranges1)
    for i in range(l - 1):
        for j in range(i + 1, l):
            r = ranges1[i]
            r2 = ranges1[j]
            if ranges_overlap(r, r2):
                r[0] = min(r[0], r2[0])
                r[1] = max(r[1], r2[1])
                r2[0] = -2
                r2[1] = -2
    delete_garbage(ranges1)


# acc_filled_ranges is accumulated filled_ranges; starts off as fr_per_db[0]
# and gets added to from each fr_per_db passed in here.
# remapped is a hash to make sure each x** of remapped lcns comes from
# the same x** of original lcns.
def eliminate_clashes(db, fr_per_db, acc_filled_ranges, filled_ranges,
        remapped):
    for r in fr_per_db:
        for r2 in acc_filled_ranges:
            if ranges_overlap(r, r2):
                clash = [max(r[0], r2[0]), min(r[1], r2[1])]
                src = clash[0] - clash[0] % 100
                cand = list(clash)
                expand = list(clash)
                expand[0] -= expand[0] % 100
                expand[1] = expand[1] - expand[1] % 100 + 99
                ok = False
                while not ok:
                    cand[0] += 100
                    cand[1] += 100
                    expand[0] += 100
                    expand[1] += 100
                    v = remapped.get(expand[0], -1)
                    if v != -1 and v != src:
                        continue
                    ok = True
                    for r3 in filled_ranges:
                        if ranges_overlap(expand, r3):
                            ok = False
                            break
                    if ok:
                        for r3 in acc_filled_ranges:
                            if ranges_overlap(cand, r3):
                                ok = False
                                break
                remapped[expand[0]] = src
                print("Moving a block of channels:")
                for i in range(clash[1] - clash[0] + 1):
                    row = db.execute("SELECT service_id, service_name " \
                            "FROM services WHERE lcn = ?",
                            [clash[0] + i]).fetchone()
                    print("%6d -> %6d : %8d | %s" % \
                            (clash[0] + i, cand[0] + i, row[0], row[1]))
                    ranges_from_lcn(cand[0] + i, acc_filled_ranges)
                    db.execute("UPDATE services SET lcn = ? " \
                            "WHERE service_id = ?", (cand[0] + i, row[0]))
                #acc_filled_ranges.sort(key = lambda x: x[0])
                #print(acc_filled_ranges)
    merge_range_lists(acc_filled_ranges, fr_per_db)
    merge_range_lists(filled_ranges, acc_filled_ranges)


def merge_dbs(db1, db2):
    table_names = db2.execute("SELECT name FROM sqlite_master "
            "WHERE type = 'table'").fetchall()
    for table in table_names:
        table = table[0]
        columns = db1.execute("PRAGMA table_info('%s')" % table).fetchall()
        qs = ", ".join('?' * len(columns))
        columns = ", ".join([c[1] for c in columns])
        db1.execute("CREATE TABLE IF NOT EXISTS %s (%s)" % (table, columns))
        rows = db2.execute("SELECT %s FROM %s" % (columns, table)).fetchall()
        db1.executemany("INSERT OR IGNORE INTO %s (%s) VALUES (%s)" % \
                (table, columns, qs), rows)


if len(sys.argv) < 3:
    show_usage()
    sys.exit(1)
elif sys.argv[1] in ('-h', '--help', '-?', '--usage'):
    show_usage()
    sys.exit(0)

dbs = []
fr_per_db = []
for a in sys.argv[1:]:
    dbs.append(sqlite3.connect(a))
filled_ranges = []
for db in dbs:
    fr = []
    ranges_from_db(db, fr)
    delete_garbage(fr)
    fr_per_db.append(fr)
    ranges_from_db(db, filled_ranges)
    delete_garbage(filled_ranges)

acc_filled_ranges = list(fr_per_db[0])
remapped = {}
for i in range(1, len(dbs)):
    eliminate_clashes(dbs[i], fr_per_db[i], acc_filled_ranges, filled_ranges,
            remapped)
    merge_dbs(dbs[0], dbs[i])


dbs[0].commit()
for db in dbs:
    db.close()

#filled_ranges.sort(key = lambda x: x[0])
#print("Filled ranges:", filled_ranges)
